package com.dfrz.stockMarket.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import com.dfrz.stockMarket.bean.MessageBean;
import com.dfrz.stockMarket.cons.Constants;
import com.dfrz.stockMarket.model.UserOrderModel;

public class UserOrderDao extends BaseDao{
	
	/*public static void main(String[] args) throws Exception {
		UserOrderModel mo=new UserOrderModel(1, 2,"huanuan", 2, 2, 1.1, (Date)new Date());
		save(mo);
		System.out.println(getUserOrderByID(1).getMsg());
	}*/
	public static MessageBean getUserOrderByID(int ID) throws Exception {
		List<UserOrderModel> list = new ArrayList<>();
		UserOrderModel userOrderModel = new UserOrderModel();
				//加载驱动
				Class.forName("oracle.jdbc.driver.OracleDriver");
				System.out.println("加载成功");
				//获取连接
				Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "hr", "hr");
				System.out.println("连接成功");
				//�?��事务
				conn.setAutoCommit(false);
				
				//获取stmt
				PreparedStatement stmt = conn.prepareStatement(" select orderid, userid, stockcode, codename, type, stockquantity, stockprice,  to_char(createdate,'yyyy-mm-dd hh24:mi:ss:ff') createdate1 from sto_userorder where userid=?");
				stmt.setInt(1, ID);

				ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					int orderid = rs.getInt("orderid");
					int userid = rs.getInt("userid");
					int stockcode = rs.getInt("stockcode");
					int type = rs.getInt("type");
					int stockquantity = rs.getInt("stockquantity");
					int stockprice = rs.getInt("stockprice");
					String codename =rs.getString("codename");
					String createdate1 =rs.getString("createdate1");
							Date date = new Date();  
					        //注意format的格式要与日期String的格式相匹配  
					        DateFormat sdf = new SimpleDateFormat("yyyy-mm-dd hh:mm:ss");  
					        try {  
					            date = sdf.parse(createdate1);  
					        } catch (Exception e) {  
					            e.printStackTrace();  
					        } 
						
					        userOrderModel.setCreateDate(date);
					        userOrderModel.setOrderID(orderid);
					        userOrderModel.setStockCode(stockcode);
					        userOrderModel.setStockPrice(stockprice);
					        userOrderModel.setStockQuantity(stockquantity);
					        userOrderModel.setType(type);
					        userOrderModel.setUserID(userid);
					        userOrderModel.setCodename(codename);
					        list.add(userOrderModel);
				}
				
				//提交or回滚
				conn.commit();
//				conn.rollback();
				//释放stmt conn
				rs.close();
				stmt.close();
				conn.close();
				
				return new MessageBean(true, list);
	}
	
	
	public static MessageBean save(UserOrderModel userOrderModel) throws Exception {
		Connection conn=returncon();
		PreparedStatement stmt = conn.prepareStatement("insert into sto_userorder( userid, stockcode, type, stockquantity, stockprice, createdate, codename)values ( ?, ?, ?, ?, ?, to_timestamp(?, 'yyyy-mm-dd hh24:mi:ss:ff'),?)");
		stmt.setInt(1, userOrderModel.getUserID());
		stmt.setInt(3, userOrderModel.getType());
		stmt.setInt(4, userOrderModel.getStockQuantity());
		stmt.setInt(2, userOrderModel.getStockCode());
		stmt.setDouble(5, userOrderModel.getStockPrice());
		stmt.setString(7, userOrderModel.getCodename());
		String dateStr = ""; 
        DateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");    
            dateStr = sdf.format(userOrderModel.getCreateDate());   
            System.out.println(dateStr); 
            stmt.setString(6,dateStr);
            int ret = stmt.executeUpdate();
    		conn.commit();
    		stmt.close();
    		conn.close();

		return new MessageBean(true, Constants.USER_SUCCESS_TYPE1);
	}


	

}
